# -*- coding: utf-8 -*-
import pandas as pd
from MySQLdb import connect, cursors
import configparser
import os
import seaborn as sns
import matplotlib.pyplot as plt
import socket

last_update_date = '2020-12-21 00:00:00'
#%%
def construct_db_connections():
    """进行数据库的连接设置，使用的是sql库,输出是库表连接，用于连接数据库
    Returns
    -------
    jy_conn: TYPE
        DESCRIPTION.
    zj_conn: TYPE
        DESCRIPTION.

    """
    hostname = socket.gethostname()
    hostname_prefix = hostname.split('-')[0]
    is_server = hostname_prefix in ('dev00', 'online00', 'online01')

    if not is_server:
        """
        In local environment, you should put a configuration file named
        '.db_config.ini' in the current directory.
        The configuration file content format must be:
            [DEFAULT]
            user=<YOUR_DB_USERNAME>
            password=<YOUR_PASSWORD>
            schema=<YOUR_OWN_SCHEMA>

        NOTE: Please do NOT commit the configuration file to svn
        """
        pwd = os.getcwd()
        db_config_file_path = os.path.join(pwd, '.db_config.ini')
        assert os.path.exists(db_config_file_path), "db config file %s is not existed" % db_config_file_path
        parser = configparser.ConfigParser()
        parser.read(db_config_file_path)
        user = parser['DEFAULT']['user']
        password = parser['DEFAULT']['password']
        zj_schema = parser['DEFAULT']['schema']
        db_host = 'dev.zhijuninvest.com'
    else:
        user = 'cronjob'
        password = 'ZyYADQjvh68UjySZ'
        db_host = '172.17.0.1'
        zj_schema = 'zj_data'

    jy_conn = connect(host=db_host, port=3306, user=user, passwd=password,
                      db="JYDB", charset="utf8mb4", cursorclass=cursors.DictCursor)
    zj_conn = connect(host=db_host, port=3306, user=user, passwd=password,
                      db=zj_schema, charset="utf8mb4", cursorclass=cursors.DictCursor)

    return jy_conn, zj_conn

def get_mf_annual_return_by_type_1():
    query = """
        SELECT SecuCode, fund_type, annual_return_12,annual_return_36,annual_return_60
        FROM zhijunfund.mf_annual_return_1
        WHERE EndDate = '%s'
        """ % (last_update_date)
    mf_annual_return = pd.read_sql(query, zj_conn)
    return mf_annual_return

def get_mf_annual_return_by_type_2():
    query = """
        SELECT SecuCode, fund_type, annual_return_12,annual_return_36,annual_return_60
        FROM zhijunfund.mf_annual_return_2
        WHERE EndDate = '%s'
        """ % (last_update_date)
    mf_annual_return = pd.read_sql(query, zj_conn)
    return mf_annual_return

def get_mf_annual_return_single(fund_type):
    query = """
        SELECT SecuCode, annual_return_12,annual_return_36,annual_return_60
        FROM zhijunfund.mf_annual_return_1
        WHERE EndDate = '%s'AND fund_type = '%s'
        """ % (last_update_date, fund_type)
    mf_annual_return = pd.read_sql(query, zj_conn)
    return mf_annual_return

def get_mf_volatility_by_type_1():
    query = """
            SELECT SecuCode, fund_type, volatility_12, volatility_24, volatility_36
            FROM zhijunfund.mf_volatility_1
            WHERE EndDate = '%s'
            """ % (last_update_date)
    mf_volatility = pd.read_sql(query, zj_conn)
    return mf_volatility

def get_mf_volatility_by_type_2():
    query = """
            SELECT SecuCode, fund_type, volatility_12, volatility_24, volatility_36
            FROM zhijunfund.mf_volatility_2
            WHERE EndDate = '%s'
            """ % (last_update_date)
    mf_volatility = pd.read_sql(query, zj_conn)
    return mf_volatility

def get_mf_maximum_drawdown_by_type_1():
    query = """
        SELECT SecuCode, fund_type, maximum_drawdown_12
        FROM zhijunfund.mf_maximum_drawdown_1
        WHERE EndDate = '%s'
        """ % (last_update_date)
    mf_maximum_drawdown = pd.read_sql(query, zj_conn)
    return mf_maximum_drawdown

def get_mf_maximum_drawdown_by_type_2():
    query = """
        SELECT SecuCode, fund_type, maximum_drawdown_12
        FROM zhijunfund.mf_maximum_drawdown_2
        WHERE EndDate = '%s'
        """ % (last_update_date)
    mf_maximum_drawdown = pd.read_sql(query, zj_conn)
    return mf_maximum_drawdown

def get_mf_var_by_type_1():
    query = """
            SELECT SecuCode, fund_type, var_12, var_24, var_36
            FROM zhijunfund.mf_var_1
            WHERE EndDate = '%s'
            """ % (last_update_date)
    mf_var = pd.read_sql(query, zj_conn)
    return mf_var

def get_mf_var_by_type_2():
    query = """
            SELECT SecuCode, fund_type, var_12, var_24, var_36
            FROM zhijunfund.mf_var_2
            WHERE EndDate = '%s'
            """ % (last_update_date)
    mf_var = pd.read_sql(query, zj_conn)
    return mf_var

if __name__ == '__main__':
    jy_conn, zj_conn = construct_db_connections()
    plt.rcParams['font.sans-serif'] = ['DengXian']
    plt.rcParams['axes.unicode_minus'] = False

    mf_annual_return = get_mf_annual_return_by_type_1()
    #print(mf_annual_return)
    df = pd.DataFrame([])
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").min()], axis = 1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").quantile(0.25)], axis=1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").mean()], axis = 1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").median()], axis = 1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").quantile(0.75)], axis=1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").max()], axis = 1)
    df.columns = ['min','q0.25', 'mean', 'median','q0.75', 'max']
    df = df.transpose()
    print(df)
    df.to_csv("mf_annual_return_by_type_1.csv", encoding = 'utf-8-sig')

    mf_annual_return = get_mf_annual_return_by_type_2()
    # print(mf_annual_return)
    df = pd.DataFrame([])
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").min()], axis=1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").quantile(0.25)], axis=1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").mean()], axis=1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").median()], axis=1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").quantile(0.75)], axis = 1)
    df = pd.concat([df, mf_annual_return[["fund_type", "annual_return_36"]].groupby("fund_type").max()], axis=1)
    df.columns = ['min','q0.25', 'mean', 'median','q0.75', 'max']
    df = df.transpose()
    print(df)
    df.to_csv("mf_annual_return_by_type_2.csv", encoding = 'utf-8-sig')

    mf_annual_return_single = get_mf_annual_return_single("货币型")
    print(mf_annual_return_single)
    mf_annual_return_single.hist("annual_return_36", bins = 30)
    plt.show()

    mf_volatility = get_mf_volatility_by_type_1()
    # print(mf_annual_return)
    df = pd.DataFrame([])
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").min()], axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").quantile(0.25)],
                   axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").mean()], axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").median()], axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").quantile(0.75)],
                   axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").max()], axis=1)
    df.columns = ['min', 'q0.25', 'mean', 'median', 'q0.75', 'max']
    df = df.transpose()
    print(df)
    df.to_csv("mf_volatility_by_type_1.csv", encoding = 'utf-8-sig')

    mf_volatility = get_mf_volatility_by_type_2()
    # print(mf_annual_return)
    df = pd.DataFrame([])
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").min()], axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").quantile(0.25)],
                   axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").mean()], axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").median()], axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").quantile(0.75)],
                   axis=1)
    df = pd.concat([df, mf_volatility[["fund_type", "volatility_36"]].groupby("fund_type").max()], axis=1)
    df.columns = ['min', 'q0.25', 'mean', 'median', 'q0.75', 'max']
    df = df.transpose()
    print(df)
    df.to_csv("mf_volatility_by_type_2.csv", encoding = 'utf-8-sig')


    mf_maximum_drawdown = get_mf_maximum_drawdown_by_type_1()
    df = pd.DataFrame([])
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").min()], axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").quantile(0.25)],
                   axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").mean()], axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").median()], axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").quantile(0.75)],
                   axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").max()], axis=1)
    df.columns = ['min', 'q0.25', 'mean', 'median', 'q0.75', 'max']
    df = df.transpose()
    print(df)
    df.to_csv("mf_maximum_drawdown_by_type_1.csv", encoding = 'utf-8-sig')

    mf_maximum_drawdown = get_mf_maximum_drawdown_by_type_2()
    df = pd.DataFrame([])
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").min()], axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").quantile(0.25)],
                   axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").mean()], axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").median()],
                   axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").quantile(0.75)],
                   axis=1)
    df = pd.concat([df, mf_maximum_drawdown[["fund_type", "maximum_drawdown_12"]].groupby("fund_type").max()], axis=1)
    df.columns = ['min', 'q0.25', 'mean', 'median', 'q0.75', 'max']
    df = df.transpose()

    print(df)
    df.to_csv("mf_maximum_drawdown_by_type_2.csv", encoding = 'utf-8-sig')

    mf_var = get_mf_var_by_type_1()

    sns.boxplot(x="fund_type", y="var_36", data=mf_var)
    plt.show()

    df = pd.DataFrame([])
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").min()], axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").quantile(0.25)],
                   axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").mean()], axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").median()], axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").quantile(0.75)],
                   axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").max()], axis=1)
    df.columns = ['min', 'q0.25', 'mean', 'median', 'q0.75', 'max']
    df = df.transpose()

    plt.show()

    print(df)

    #df.to_csv("mf_var_by_type_1.csv", encoding = 'utf-8-sig')

    mf_var = get_mf_var_by_type_2()
    print(mf_var)
    df = pd.DataFrame([])
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").min()], axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").quantile(0.25)],
                   axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").mean()], axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").median()], axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").quantile(0.75)],
                   axis=1)
    df = pd.concat([df, mf_var[["fund_type", "var_36"]].groupby("fund_type").max()], axis=1)
    df.columns = ['min', 'q0.25', 'mean', 'median', 'q0.75', 'max']
    df = df.transpose()
    print(df)

    print(df.columns)


    #
    # card topics
    #
    category_list = ['股票型','混合型-偏股混合型','混合型-标准混合型','混合型-偏债混合型','债券型-进取债型','债券型-纯债型','债券型-可转债型']
    category_map = {
        '股票型':'1股票型',
        '混合型-偏股混合型':'2混合型-偏股混合型',
        '混合型-标准混合型':'3混合型-标准混合型',
        '混合型-偏债混合型':'4混合型-偏债混合型',
        '债券型-进取债型':'5债券型-进取债型',
        '债券型-可转债型':'6债券型-可转债型',
        '债券型-纯债型':'7债券型-纯债型',
    }

    mf_annual_return = get_mf_annual_return_by_type_2()
    mf_annual_return1 = mf_annual_return[mf_annual_return['fund_type'].isin(category_list)]
    mf_annual_return1['fund_type'] = mf_annual_return1['fund_type'].apply(lambda x: category_map[x])
    mf_annual_return1 = mf_annual_return1.sort_values(by=['fund_type'])

    mf_volatility = get_mf_volatility_by_type_2()
    mf_volatility1 = mf_volatility[mf_volatility['fund_type'].isin(category_list)]
    mf_volatility1['fund_type'] = mf_volatility1['fund_type'].apply(lambda x: category_map[x])
    mf_volatility1 = mf_volatility1.sort_values(by=['fund_type'])

    mf_maximum_drawdown = get_mf_maximum_drawdown_by_type_2()
    mf_maximum_drawdown1 = mf_maximum_drawdown[mf_maximum_drawdown['fund_type'].isin(category_list)]
    mf_maximum_drawdown1['fund_type'] = mf_maximum_drawdown1['fund_type'].apply(lambda x: category_map[x])
    mf_maximum_drawdown1 = mf_maximum_drawdown1.sort_values(by=['fund_type'])

    mf_var = get_mf_var_by_type_2()
    mf_var1 = mf_var[mf_var['fund_type'].isin(category_list)]
    mf_var1['fund_type'] = mf_var1['fund_type'].apply(lambda x: category_map[x])
    mf_var1 = mf_var1.sort_values(by=['fund_type'])

    sns.boxplot(x="fund_type", y="annual_return_12", data=mf_annual_return1, showfliers=False)
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig("card_ret.png")
    plt.show()


    #df.to_csv("mf_var_by_type_2.csv", encoding = 'utf-8-sig')



